package Controllor;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Observable;
import java.util.Set;
import java.util.TreeSet;

import Model.Apartment;
import Model.Employee;
import Model.Lease;
import Model.Person;
import Model.Resident;

public class DB extends Observable {

	private String userName = "_445team19";
	private String password = "Fuphif";
	private String serverName = "jdbc:mysql://cssgate.insttech.washington.edu/_445team19";
	private Connection conn;
	private PreparedStatement statement;

	private List<Employee> employees;

	private List<Lease> leases;

	private List<Apartment> apartments;

	private List<Resident> residents;
	
	private List<Person> persons;

	public DB() throws Exception {
		leases = new ArrayList<Lease>();
		persons = new ArrayList<Person>();
		employees = new ArrayList<Employee>();
		apartments = new ArrayList<Apartment>();
		residents = new ArrayList<Resident>();
		populateEmployees();
		populateResident();
		populateLease();
		populatePersons();
		populateApartment();
	}
	public Connection getConnection() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(serverName, userName, password);
		conn.setAutoCommit(false);  
		return conn;                                                       
	}

	public void closeConnection(PreparedStatement statement,
			ResultSet results) throws Exception {
		if (statement != null) {
			statement.close();
			statement = null;
		}
		if (results != null) {
			results.close();
			results = null;
		}
		if (conn != null) {
			conn.close();
			conn = null;
		}
	}

	public List<Employee> getEmployees() {
		return employees;
	}

	public List<Lease> getLeases() {
		return leases;
	}

	public List<Apartment> getApartments() {
		return apartments;
	}

	public List<Resident> getResidents() {
		return residents;
	}
	
	public List<Person> getPersons() {
		return persons;
	}
	
	public void populatePersons() throws Exception {
		Person person = null;
		ResultSet rs = null;
		try {
			// step 1: connect to database
			getConnection();
			// step 2: prepare the statement and fill it
			statement = conn.prepareStatement("SELECT * FROM _445team19.PERSON");
			// step 3: execute and get the results
			rs = statement.executeQuery();
			while(rs.next()){
				int id = rs.getInt("PersonID");
				String fName = rs.getString("PersonFname");
				String mInit = rs.getString("PersonMinit");
				String lName = rs.getString("PersonLname");
				String dob = rs.getString("PersonDOB");
				int phone = rs.getInt("PersonPhone");
				String licensePerson = rs.getString("PersonLicensePlateNumber");
				String licenseState = rs.getString("PersonLicensePlateState");
				String contactName = rs.getString("PersonEmergencyContactName");
				String contactNumber = rs.getString("PersonEmergencyContactNumber");
				person = new Person(id, fName, mInit, lName, dob, phone, licensePerson, licenseState, contactName, contactNumber);
				persons.add(person);
			}
		} finally {
			// step 4: close the connection
			closeConnection(statement, rs);
		}		
	}

	public void populateEmployees() throws Exception {
		Employee employee = null;
		ResultSet rs = null;
		try {
			// step 1: connect to database
			getConnection();
			// step 2: prepare the statement and fill it
			statement = conn.prepareStatement("SELECT * FROM _445team19.EMPLOYEE");
			// step 3: execute and get the results
			rs = statement.executeQuery();
			while(rs.next()){
				employee = new Employee(0, 0, password, 0);
				employee.setID(rs.getInt("EmployeeID"));
				employee.setPos(rs.getInt("EmployeePosition"));
				employee.setComplexName(rs.getString("EmployeeCOMPLEX_ComplexName"));
				employee.setPersonID(rs.getInt("EmployeePERSON_PersonID"));
				employees.add(employee);
			}
		} finally {
			// step 4: close the connection
			closeConnection(statement, rs);
		}
	}

	public void populateApartment() throws Exception {
		Apartment apartment = null;
		ResultSet set = null;
		try {
			// step 1: connect to database
			getConnection();
			// step 2: prepare the statement and fill it
			statement = conn.prepareStatement("SELECT * FROM _445team19.APARTMENT");
			// step 3: execute and get the results
			set = statement.executeQuery();
			while (set.next()) {
				apartment = new Apartment(null, null, 0, 0, 0, 0, 0, 0, null);
				apartment.setNumber(set.getString("ApartmentNumber"));
				apartment.setFloorPlan(set.getString("ApartmentFloorPlan"));
				apartment.setSize(set.getInt("ApartmentFloorSize"));
				apartment.setCarPortNumber(set.getInt("ApartmentCarPortNumber"));
				apartment.setBaseCost(set.getDouble("ApartmentBaseCost"));
				apartment.setUtilitiesCost(set.getDouble("ApartmentUtilitiesCost"));
				apartment.setGateCode(set.getInt("ApartmentGateCode"));
				apartment.setMailBoxNumber(set.getInt("ApartmentMailBoxNumber"));
				apartment.setComplexName(set.getString("Complex_ComplexName"));
				apartments.add(apartment);
			}
		} finally {
			// step 4: close the connection
			closeConnection(statement, set);
		}
	}
	
	public void populateResident() throws Exception {
		Resident resident = null;
		ResultSet set = null;
		try {
			// step 1: connect to database
			getConnection();
			// step 2: prepare the statement and fill it
			statement = conn.prepareStatement("SELECT * FROM _445team19.RESIDENT");
			// step 3: execute and get the results
			set = statement.executeQuery();
			while (set.next()) {
				resident = new Resident(0, null, null, null, null, 0, 0);
				resident.setEmail(set.getString("ResidentEmail"));
				resident.setID(set.getInt("ResidentID"));
				resident.setLicenseNumber(set.getString("ResidentLicenseNumber"));
				resident.setLicenseState(set.getString("ResidentLicenseState"));
				resident.setPersonID(set.getInt("PERSON_ID"));
				resident.setWorkPhone(set.getInt("ResidentWorkPhone"));
				resident.setWorkPlace(set.getString("ResidentWorkPlace"));
				residents.add(resident);
				}
			} finally {
				// step 4: close the connection
				closeConnection(statement, set);
			}
	}
	
	public void populateLease() throws Exception{
		Lease lease = null;
		ResultSet set = null;
		try {
			// step 1: connect to database
			getConnection();
			// step 2: prepare the statement and fill it
			statement = conn.prepareStatement("SELECT * FROM _445team19.LEASE");
			// step 3: execute and get the results
			set = statement.executeQuery();
			while (set.next()) {
				lease = new Lease(null, 0, 0, 0, 0, 0, null, null, null, 0, 0);
				lease.setStartDate(set.getString("LeaseStartDate"));
				lease.setTerm(set.getInt("LeaseTerm"));
				lease.setNumberOfPets(set.getInt("LeaseNumberOfPets"));
				lease.setEmployeeID(set.getInt("EMPLOYEE_EmployeeID"));
				lease.setResidentID(set.getInt("RESIDENT_ResidentID"));
				lease.setDepositPaid(set.getDouble("LeaseDepositPaid"));
				lease.setMonthlyDiscount(set.getDouble("LeaseMonthlyDiscount"));
				lease.setPetDepositPaid(set.getDouble("LeasePetDepositPaid"));
				lease.setRentersInsuranceCarrier(set.getString("LeaseRentersInsuranceCarrier"));
				lease.setRentersInsuranceID(set.getString("LeaseRentersInsuranceID"));
				lease.setApartmentNumber(set.getString("APARTMENT_ApartmentNumber"));
				leases.add(lease);
			}
		} finally {
			// step 4: close the connection
			closeConnection(statement, set);
		}
	}
	
	public void addLease(String startDate, int term, double deposit, double monthlyDiscount, int pets, double petDeposit, String insuranceCarrier, String insuranceID, String apartmentNumber, int employeeID, int residentID) {
		if (startDate != null && term >= 0 && deposit >= 0 && monthlyDiscount >= 0 && pets >= 0 && pets < 10 && petDeposit >= 0 && insuranceCarrier != null && insuranceID != null && apartmentNumber != null && employeeID >= 0 && residentID >= 0) {
	 		Lease lease = new Lease(startDate, term, deposit, monthlyDiscount, pets, petDeposit, insuranceCarrier, insuranceID, apartmentNumber, employeeID, residentID);
			Connection con = null;
			try {
				con = getConnection();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {

				Statement statement = con.createStatement();
				statement.execute("INSERT INTO LEASE VALUES (\'" + startDate + "\', " + term + ", " + deposit + ", " + monthlyDiscount + ", " + pets + ", " + petDeposit + ", \'" + insuranceCarrier + "\', \'" + insuranceID + "\', \'" + apartmentNumber + "\', " + employeeID + ", " + residentID + ");");
				leases.add(lease);
				setChanged();
				notifyObservers();
			} catch (Exception ex) {
				System.err.println("Error: " + ex);
			}
		}
	}
	
	public void addResident(int id, String email, String licenseNumber, String licenseState, String workPlace, int workPhone, int person_ID) throws SQLException {
		if (email != null && licenseNumber != null && licenseState != null && workPlace != null && workPhone >= 0 && person_ID >= 0) {
	 		
			Connection con = null;
			try {
				con = getConnection();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				Statement statement = con.createStatement();
				statement.execute("INSERT INTO RESIDENT VALUES (" + id + ", \'" + email + "\', \'" + licenseNumber + "\', \'" + licenseState + "\', \'" + workPlace + "\', " + workPhone + ", " + person_ID + ");");
			} catch (Exception ex) {
				System.err.println("Error: " + ex);
			}

		} else {
			System.out.println("Not correct info");
		}
		setChanged();
		notifyObservers();
	}
	
	public void addPerson(int id, String fName, String mInit, String lName, String dOB, int phone, String licensePlateNumber, String licensePlateState, String emergencyContact, String emergencyContactNumber) throws Exception {
		if (fName != null && mInit != null && lName != null && dOB != null && phone > 0 && licensePlateNumber != null && licensePlateState != null && emergencyContact != null && emergencyContactNumber != null) {
			Connection con = null;
			ResultSet set = null;
			try {
				con = getConnection();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				Statement statement = con.createStatement();
				Statement statement2 = con.createStatement();
				statement.execute("INSERT INTO PERSON VALUES (" + id + ", \'" + fName + "\', \'" + mInit + "\', \'" + lName + "\', \'" + dOB + "\', " + phone + ", \'" + licensePlateNumber + "\', \'" + licensePlateState + "\', \'" + emergencyContact + "\', " + emergencyContactNumber + ");");
			} catch (Exception ex) {
				System.err.println("Error: " + ex);
			}
		}		
	}
	
	public void updateLease(String startDate, int term, double deposit, double monthlyDiscount, int pets, double petDeposit, String insuranceCarrier, String insuranceID, String apartmentNumber, int employeeID, int residentID) {
		if (startDate != null && term >= 0 && deposit >= 0 && monthlyDiscount >= 0 && pets >= 0 && pets < 10 && petDeposit >= 0 && insuranceCarrier != null && insuranceID != null && apartmentNumber != null && employeeID >= 0 && residentID >= 0) {
			Connection con = null;
			try {
				con = getConnection();
			} catch (Exception e) {				
				e.printStackTrace();
			}
			try {
				Statement statement = con.createStatement();
				statement.execute("UPDATE LEASE SET LeaseTerm=" + term + ", LeaseDepositPaid=" + deposit + ", LeaseMonthlyDiscount=" + monthlyDiscount + ", LeaseNumberOfPets=" + pets + ", LeasePetDepositPaid=" + petDeposit + ", LeaseRentersInsuranceCarrier=\'" + insuranceCarrier + "\', LeaseRentersInsuranceID=\'" + insuranceID + "\' WHERE (LeaseStartDate=\'" + startDate + "\' AND APARTMENT_ApartmentNumber=\'" + apartmentNumber + "\');");
				for (int i = 0; i < leases.size(); i++) {
					Lease l = leases.get(i);
					if (l.getStartDate().equals(startDate) && l.getApartmentNumber().equals(apartmentNumber)) {
						l.setTerm(term);
						l.setDepositPaid(deposit);
						l.setMonthlyDiscount(monthlyDiscount);
						l.setNumberOfPets(pets);
						l.setPetDepositPaid(petDeposit);
						l.setRentersInsuranceCarrier(insuranceCarrier);
						l.setRentersInsuranceID(insuranceID);						
						break;
					}
				}
				setChanged();
				notifyObservers();
			} catch (Exception ex) {
				System.err.println("Error: " + ex);
			}
		}
	}
	
	public void removeLease(String startDate, String apartmentNumber) {
		Connection con = null;
		try {
			con = getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			Statement statement = con.createStatement();
			statement.execute("DELETE FROM LEASE WHERE(LeaseStartDate=" + "\'" + startDate + "\' AND APARTMENT_ApartmentNumber=\'" + apartmentNumber +"\');");
		} catch (Exception ex) {
			System.err.println("Error: " + ex);
		}
		
		for (int i = 0; i < leases.size(); i++) {
			Lease l = leases.get(i);
			if (l.getStartDate().equals(startDate) && l.getApartmentNumber().equals(apartmentNumber)) {
				leases.remove(i);
			}
			break;
		}
		setChanged();
		notifyObservers();
	}
	
	public Person getPerson(int personID) {
		Person p = null;
		for (int i = 0; i < persons.size(); i++) {
			if (persons.get(i).getID() == personID) {
				p = persons.get(i);
				break;
			}	
		}
		return p;
	}
}
